package com.jeecg.demo.service.impl;

import com.jeecg.demo.entity.TradeorderEntity;
import com.jeecg.demo.entity.TraderStatistics;
import com.jeecg.demo.service.TradeorderServiceI;
import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.Serializable;
import java.util.List;

@Service("tradeorderService")
@Transactional
public class TradeorderServiceImpl extends CommonServiceImpl implements TradeorderServiceI {

	@Autowired
	private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

 	public void delete(TradeorderEntity entity) throws Exception{
 		super.delete(entity);
 	}

 	public Serializable save(TradeorderEntity entity) throws Exception{
 		Serializable t = super.save(entity);
 		return t;
 	}

 	public void saveOrUpdate(TradeorderEntity entity) throws Exception{
 		super.saveOrUpdate(entity);
 	}

	@Override
	public List<TraderStatistics> getTraderStatisticsList(String corporateName, String beginDate, String endDate) {
		 String sql = "\n" +
				 "with gp as\n" +
				 " (select t.shul,\n" +
				 "         t.jiag,\n" +
				 "         r.poid,\n" +
				 "         p.trademodeid,\n" +
				 "         b.maxpri,\n" +
				 "         b.minpri,\n" +
				 "         to_char(t.toid) toid,\n" +
				 "         t.meiz,\n" +
				 "         t.pinp,\n" +
				 "         t.chumdwmc,\n" +
				 "         t.qianysj,\n" +
				 "         p.direction\n" +
				 "    from soa_mvecp_xm.v_ecp_het_all t\n" +
				 "    left join (select max(toid) as toid,\n" +
				 "                     max(o.toprice) as maxtojiag,\n" +
				 "                     min(o.toprice) as mintojiag,\n" +
				 "                     o.poid\n" +
				 "                from soa_mvecp_xm.tradeorder o\n" +
				 "               group by poid) r\n" +
				 "      on t.toid = r.toid\n" +
				 "    left join (select e.poid,e.direction,\n" +
				 "                     max(e.poprice) as maxjiag,\n" +
				 "                     min(e.poprice) as minjiag,\n" +
				 "                     trademodeid\n" +
				 "                from soa_mvecp_xm.postorder e\n" +
				 "               group by poid, trademodeid,direction) p\n" +
				 "      on p.poid = r.poid\n" +
				 "    left join (select a.poid,\n" +
				 "                     max(a.aoprice) as maxpri,\n" +
				 "                     min(a.aoprice) as minpri\n" +
				 "                from soa_mvecp_xm.acceptorder a\n" +
				 "              \n" +
				 "               group by poid) b\n" +
				 "      on p.poid = b.poid\n" +
				 "   where t.toid is not null\n" +
				 "     and p.trademodeid = '1'),\n" +
				 "\n" +
				 "\n" +
				 "jj as\n" +
				 " (select t.shul,\n" +
				 "         t.jiag,\n" +
				 "         r.poid,\n" +
				 "         p.trademodeid,\n" +
				 "         b.maxpri,\n" +
				 "         b.minpri,\n" +
				 "         to_char(r.toid) toid,\n" +
				 "         t.meiz,\n" +
				 "         t.pinp,\n" +
				 "         t.chumdwmc,\n" +
				 "         t.qianysj,\n" +
				 "         p.direction\n" +
				 "    from soa_mvecp_xm.v_ecp_het_all t\n" +
				 "    left join (select max(toid) as toid,\n" +
				 "                     max(o.toprice) as maxtojiag,\n" +
				 "                     min(o.toprice) as mintojiag,\n" +
				 "                     o.poid\n" +
				 "                from soa_mvecp_xm.tradeorder o\n" +
				 "               group by o.toid, poid) r\n" +
				 "      on t.toid = r.toid\n" +
				 "    left join (select e.poid,e.direction,\n" +
				 "                     max(e.poprice) as maxjiag,\n" +
				 "                     min(e.poprice) as minjiag,\n" +
				 "                     trademodeid\n" +
				 "                from soa_mvecp_xm.postorder e\n" +
				 "               group by poid, trademodeid,direction) p\n" +
				 "      on p.poid = r.poid\n" +
				 "    left join (select a.poid,\n" +
				 "                     max(a.aoprice) as maxpri,\n" +
				 "                     min(a.aoprice) as minpri\n" +
				 "                from soa_mvecp_xm.acceptorder a\n" +
				 "             \n" +
				 "               group by poid) b\n" +
				 "      on p.poid = b.poid\n" +
				 "   where t.toid is not null\n" +
				 "     and p.trademodeid = '3'),\n" +
				 "\n" +
				 "\n" +
				 "zqht as\n" +
				 " (select t.shul,\n" +
				 "         t.jiag as jiag,\n" +
				 "         0 as poid,\n" +
				 "         33 as trademodeid,\n" +
				 "         0 as maxpri,\n" +
				 "         0 as minpri,\n" +
				 "         to_char(t.toid) toid,\n" +
				 "         t.meiz,\n" +
				 "         t.pinp,\n" +
				 "         t.chumdwmc,\n" +
				 "         t.qianysj,\n" +
				 "         'S' as direction\n" +
				 "    from soa_mvecp_xm.v_ecp_het_all t\n" +
				 "   where t.dongzbm = '33'\n" +
				 "     and t.zhuangt = '4'),\n" +
				 "\n" +
				 "\n" +
				 "cxht as\n" +
				 " (select t.shul,\n" +
				 "         t.jiag as jiag,\n" +
				 "         0 as poid,\n" +
				 "         32 as trademodeid,\n" +
				 "         0 as maxpri,\n" +
				 "         0 as minpri,\n" +
				 "         to_char(t.toid) toid,\n" +
				 "         t.meiz,\n" +
				 "         t.pinp,\n" +
				 "         t.chumdwmc,\n" +
				 "         t.qianysj,\n" +
				 "         'S' as direction\n" +
				 "    from soa_mvecp_xm.v_ecp_het_all t\n" +
				 "   where t.dongzbm = '32'\n" +
				 "     and t.zhuangt = '4')\n" +
				 "\n" +
				 "select case when count(1) = 0 then 1 else count(1) end as intotal ,count(case\n" +
				 "               WHEN trademodeid = 1 then\n" +
				 "                1\n" +
				 "             end) as gpcount,  \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 1 then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as gpzb, \n" +
				 "       \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 1 then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as gpsum,\n" +
				 "       max(case\n" +
				 "             when trademodeid = 1 then\n" +
				 "              jiag\n" +
				 "           end) as gpzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 1 then\n" +
				 "              jiag\n" +
				 "           end) as gpzddj, \n" +
				 "       (max(case\n" +
				 "              when trademodeid = 1 then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 1 then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as meanjiag, \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 1 then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 1 then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as jinge,         \n" +
				 "             \n" +
				 "             \n" +
				 "       count(case\n" +
				 "               WHEN trademodeid = 3 then\n" +
				 "                3\n" +
				 "             end) as jjcount, \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 3 then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as jjzb, \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 3 then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as jjsum, \n" +
				 "       max(case\n" +
				 "             when trademodeid = 3 then\n" +
				 "              jiag\n" +
				 "           end) as jjzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 3 then\n" +
				 "              jiag\n" +
				 "           end) as jjzddj, \n" +
				 "        (max(case\n" +
				 "              when trademodeid = 3 then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 3 then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as meanbiddingjiag,\n" +
				 "        round(sum(case\n" +
				 "                   when trademodeid = 3 then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 3 then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as biddingjinge,            \n" +
				 "                \n" +
				 "       count(case\n" +
				 "               WHEN trademodeid = 33 then\n" +
				 "                33\n" +
				 "             end) as zqcount,\n" +
				 "             \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 33 then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as zqzb,\n" +
				 "       \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 33 then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as zqsum, \n" +
				 "       max(case\n" +
				 "             when trademodeid = 33 then\n" +
				 "              jiag\n" +
				 "           end) as zqzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 33 then\n" +
				 "              jiag\n" +
				 "           end) as zqzddj, \n" +
				 "       (max(case\n" +
				 "              when trademodeid = 33 then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 33 then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as sidejiag, \n" +
				 "        round(sum(case\n" +
				 "                   when trademodeid = 33 then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 33 then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as sidejinge, \n" +
				 "       count(case\n" +
				 "               WHEN trademodeid = 32 then\n" +
				 "                32\n" +
				 "             end) as cxcount, \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 32 then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as cxzb,\n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 32 then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as cxsum, \n" +
				 "        max(case\n" +
				 "             when trademodeid = 32 then\n" +
				 "              jiag\n" +
				 "           end) as cxzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 32 then\n" +
				 "              jiag\n" +
				 "           end) as cxzddj, \n" +
				 "       (max(case\n" +
				 "              when trademodeid = 32 then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 32 then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as cxjiag,  \n" +
				 "        round(sum(case\n" +
				 "                   when trademodeid = 32 then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 32 then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as cxjinge, \n" +
				 "         \n" +
				 "        count(case\n" +
				 "               WHEN trademodeid = 1 and  Substr(qianysj, 0, 7) = subStr('"+endDate+"', 0, 7) and chumdwmc = '"+corporateName+"' then\n" +
				 "                1\n" +
				 "             end) as bqgpcount,  \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as bqgpzb, \n" +
				 "       \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as bqgpsum, \n" +
				 "       max(case\n" +
				 "             when trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqgpzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqgpzddj, \n" +
				 "       (max(case\n" +
				 "              when trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as bqmeanjiag, \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 1 then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 1 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"' ) and chumdwmc = '"+corporateName+"' then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as bqjinge,        \n" +
				 "             \n" +
				 "             \n" +
				 "       count(case\n" +
				 "               WHEN trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                3\n" +
				 "             end) as bqjjcount, \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as bqjjzb, \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as bqjjsum, \n" +
				 "       max(case\n" +
				 "             when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqjjzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqjjzddj, \n" +
				 "        (max(case\n" +
				 "              when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as bqmeanbiddingjiag,   \n" +
				 "        round(sum(case\n" +
				 "                   when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 3 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as bqbiddingjinge,         \n" +
				 "                \n" +
				 "       count(case\n" +
				 "               WHEN trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                33\n" +
				 "             end) as bqzqcount, \n" +
				 "             \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as bqzqzb,\n" +
				 "       \n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as bqzqsum, \n" +
				 "       max(case\n" +
				 "             when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqzqzgdj,\n" +
				 "       min(case\n" +
				 "             when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqzqzddj, \n" +
				 "       (max(case\n" +
				 "              when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as bqsidejiag,  \n" +
				 "        round(sum(case\n" +
				 "                   when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 33 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as bqsidejinge,     \n" +
				 "       count(case\n" +
				 "               WHEN trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                32\n" +
				 "             end) as bqcxcount, \n" +
				 "       round((count(case\n" +
				 "               WHEN trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                1\n" +
				 "             end))/case when count(1) = 0 then 1 else count(1) end * 100,2) as bqcxzb,\n" +
				 "       round(sum(case\n" +
				 "                   when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) / 10000,\n" +
				 "             2) as bqcxsum, \n" +
				 "        max(case\n" +
				 "             when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqcxzgdj, \n" +
				 "       min(case\n" +
				 "             when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "              jiag\n" +
				 "           end) as bqcxzddj, \n" +
				 "       (max(case\n" +
				 "              when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "               jiag\n" +
				 "            end) + min(case\n" +
				 "                          when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                           jiag\n" +
				 "                        end)) / 2 as bqcxjiag, \n" +
				 "        round(sum(case\n" +
				 "                   when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                    shul\n" +
				 "                 end) * sum(case\n" +
				 "                              when trademodeid = 32 and (qianysj<='"+endDate+"' and qianysj >= '"+beginDate+"') and chumdwmc = '"+corporateName+"' then\n" +
				 "                               jiag\n" +
				 "                            end) / 1000000,\n" +
				 "             2) as bqcxjinge \n" +
				 "  from (select *\n" +
				 "          from gp\n" +
				 "        union all\n" +
				 "        select *\n" +
				 "          from jj\n" +
				 "        union all\n" +
				 "        select *\n" +
				 "          from zqht\n" +
				 "        union all\n" +
				 "        select * from cxht) total  where 1=1\n";
		List<TraderStatistics> traderStatisticsList = this.findListbySql(sql);
		return traderStatisticsList;
	}

}
